package com.hbue.dao.impl;

import com.hbue.bean.Book;
import com.hbue.dao.BookDao;
import com.hbue.util.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;

public class BookDaoImpl implements BookDao {

    @Override
    public ArrayList<Book> findAllBook() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            statement = connection.prepareStatement("select * from book ");
            ResultSet resultSet = statement.executeQuery();
            ArrayList<Book> books = new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String author = resultSet.getString(3);
                String publisher = resultSet.getString(4);
                Date date = resultSet.getDate(5);
                double price = resultSet.getDouble(6);
                String usage = resultSet.getString(7);
                String ISBN = resultSet.getString(8);
                Book book = new Book(id, bookName, author, publisher, date, price, usage, ISBN);
                books.add(book);
            }
            return books;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    /**
     * 根据id删除图书
     * @param id
     */
    @Override
    public void deleteById(String id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            statement = connection.prepareStatement("delete from book where bookId= ?");
            statement.setString(1,id);
            statement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public Book findBook(String id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            statement = connection.prepareStatement("select * from book where bookId = ?");
            statement.setInt(1,Integer.parseInt(id));
            ResultSet resultSet = statement.executeQuery();
            Book book = new Book();
            while (resultSet.next()){
                int bookId = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String author = resultSet.getString(3);
                String publisher = resultSet.getString(4);
                Date date = resultSet.getDate(5);
                double price = resultSet.getDouble(6);
                String usage = resultSet.getString(7);
                String ISBN = resultSet.getString(8);
                book = new Book(bookId,bookName,author,publisher,date,price,usage,ISBN);
            }
            return book;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void update(Book book) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            String sql = "UPDATE  book  SET  bookName=?,author=?,publisher=?,publishdate=?,price=?,booktype=?,isbn=?  WHERE  bookId=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,book.getBookName());
            statement.setString(2,book.getAuthor());
            statement.setString(3,book.getPublisher());
            statement.setDate(4,new java.sql.Date(book.getDate().getTime()));
            statement.setDouble(5,book.getPrice());
            statement.setString(6,book.getUsage());
            statement.setString(7,book.getISBN());
            statement.setInt(8,book.getId());
            statement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void add(Book book) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            statement = connection.prepareStatement("insert  into book(bookName,author,publisher,publishdate,price,booktype,isbn) values (?,?,?,?,?,?,?)");
            statement.setString(1,book.getBookName());
            statement.setString(2,book.getAuthor());
            statement.setString(3,book.getPublisher());
            statement.setDate(4,new java.sql.Date(book.getDate().getTime()));
            statement.setDouble(5,book.getPrice());
            statement.setString(6,book.getUsage());
            statement.setString(7,book.getISBN());
            statement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     *      查询搜索内容
     * @param searchContent
     * @return
     */
    @Override
    public ArrayList<Book> findBookBySearchContent(String searchContent) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtil.getConnection();
            String  sql = "select * from book where bookId =? or bookName=? or author=? or publisher=? or isbn=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,searchContent);
            statement.setString(2,searchContent);
            statement.setString(3,searchContent);
            statement.setString(4,searchContent);
            statement.setString(5,searchContent);
            ResultSet resultSet = statement.executeQuery();
            ArrayList<Book> books = new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String author = resultSet.getString(3);
                String publisher = resultSet.getString(4);
                Date date = resultSet.getDate(5);
                double price = resultSet.getDouble(6);
                String usage = resultSet.getString(7);
                String isbn = resultSet.getString(8);
                Book book = new Book(id, bookName, author, publisher, date, price, usage, isbn);
                books.add(book);
            }
            return books;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
